<?php
class ModelCatalogProductSort extends Model {
    public function editSortOrderCategory1($data){
        if(!isset($data['selected'])){
            return;
        }
        
        foreach($data['selected'] as $product_id){
            foreach($data['sort'][$product_id] as $sort){
                // Select a record will be change sort order
                $sql = "SELECT * FROM " . DB_PREFIX . "product_category_sort " .                                             
                    " WHERE category_id = '" . (int)$sort['category_id'] . "' AND product_sort_order = '" . (int)$sort['sort_order'] . "'";                   
                $query1 = $this->db->query($sql); 
                
                // Select sort order for record is being update
                $sql = "SELECT * FROM " . DB_PREFIX . "product_category_sort " .                                             
                    " WHERE category_id = '" . (int)$sort['category_id'] . "' AND product_id = '" . (int)$product_id . "'";                   
                $query2 = $this->db->query($sql); 
                
                // Update sort order for another records
                $need_update = $query1->row && $query1->row['product_id'] != $product_id;
                if($need_update){                        
                    $sql = "UPDATE " . DB_PREFIX . "product_category_sort SET " .                         
                        " product_sort_order = product_sort_order + 1" .
                        " WHERE category_id = '" . (int)$sort['category_id'] . "' AND product_sort_order >= '" . (int)$sort['sort_order'] . "'";                   
                    
                    if(isset($query2->row['product_sort_order'])){
                        $sql .= " AND product_sort_order < '" . (int)$query2->row['product_sort_order'] . "'";
                    }
                    
                    $this->db->query($sql);
                }
               
                // Process sort for current record
                if($sort['sort_id'] > 0){
                    if(!$query1->num_rows || $need_update){
                        $sql = "UPDATE " . DB_PREFIX . "product_category_sort SET " .                         
                            " product_sort_order = '" . (int)$sort['sort_order'] . "'" .
                            " WHERE product_id = '" . (int)$product_id . "' AND category_id = '" . (int)$sort['category_id'] . "'";    
                    }
                }
                else{
                    $sql = "INSERT INTO " . DB_PREFIX . "product_category_sort SET " .
                        " product_id = '" . (int)$product_id . "', " .
                        " category_id = '" . (int)$sort['category_id'] . "', " .
                        " product_sort_order = '" . (int)$sort['sort_order'] . "'";                    
                }
                
                $this->db->query($sql);                 
            }
        }		
    }
    
    public function editSortOrderCategory($data){
        if(!isset($data['selected'])){
            return;
        }
        
        /*
        ** Convert data from product_id as key to category_id as key
        ** From: product_id => array(
        **          array(category_id =>...), array(category_id =>...),...
        **      )
        ** To: category_id => array(
        **          array(product_id =>...), array(product_id =>...), ...
        **      )
        */
        $new_sorts = array();
        foreach($data['selected'] as $product_id){  						
			foreach($data['sort'][$product_id] as $sort) {
                $new_sorts[$sort['category_id']][] = array(
                    'sort_id'           => $sort['sort_id'],
                    'product_id'        => $product_id,
                    'sort_order'        => $sort['sort_order'], // > 0 ? $sort['sort_order'] : 1,
                    'sort_order_old'    => $sort['sort_order_old'], // > 0 ? $sort['sort_order_old'] : 1,
                    'category_id'       => $sort['category_id'],
                );
            }
        }
                
        foreach($new_sorts as &$sort_item){
            // Sort data descending
            $sort_order = array();
            foreach($sort_item as $key => $value){
                $sort_order[$key] = $value;
            }
            
            array_multisort($sort_order, SORT_DESC, $sort_item);
            
            // Add to database
            foreach($sort_item as $sort_data){
                if($sort_data['sort_order'] != $sort_data['sort_order_old']){
                    $this->processSortByCategory($sort_data);
                }
            }
        }                    
    }
    
    public function processSortByCategory($sort){
        // Select a record will be change sort order
        $sql = "SELECT * FROM " . DB_PREFIX . "product_category_sort " .                                             
            " WHERE category_id = '" . (int)$sort['category_id'] . "' AND product_sort_order = '" . (int)$sort['sort_order'] . "'";                   
        $query1 = $this->db->query($sql); 
        
        // Select sort order for record is being update
        $sql = "SELECT * FROM " . DB_PREFIX . "product_category_sort " .                                             
            " WHERE category_id = '" . (int)$sort['category_id'] . "' AND product_id = '" . (int)$sort['product_id'] . "'";                   
        $query2 = $this->db->query($sql); 
        
        // Update sort order for another records
        $need_update = $query1->num_rows && $query1->row['product_id'] != $product_id;
        if($need_update){                        
            $sql = "UPDATE " . DB_PREFIX . "product_category_sort SET " .                         
                " product_sort_order = product_sort_order + 1" .
                " WHERE category_id = '" . (int)$sort['category_id'] . "' AND product_sort_order >= '" . (int)$sort['sort_order'] . "'";                   
            
            if(isset($query2->row['product_sort_order'])){
                $sql .= " AND product_sort_order < '" . (int)$query2->row['product_sort_order'] . "'";
            }
            
            $this->db->query($sql);
        }
       
        // Process sort for current record
        if($sort['sort_id'] > 0){
            if(!$query1->num_rows || $need_update){
                $sql = "UPDATE " . DB_PREFIX . "product_category_sort SET " .                         
                    " product_sort_order = '" . (int)$sort['sort_order'] . "'" .
                    " WHERE product_id = '" . (int)$sort['product_id'] . "' AND category_id = '" . (int)$sort['category_id'] . "'";    
            }
        }
        else{
            $sql = "INSERT INTO " . DB_PREFIX . "product_category_sort SET " .
                " product_id = '" . (int)$sort['product_id'] . "', " .
                " category_id = '" . (int)$sort['category_id'] . "', " .
                " product_sort_order = '" . (int)$sort['sort_order'] . "'";                    
        }
        
        $this->db->query($sql);     
    }
    
    public function addSortByCategory($sort){
        $sql = "INSERT INTO " . DB_PREFIX . "product_category_sort SET " .
            " product_id = '" . (int)$sort['product_id'] . "', " .
            " category_id = '" . (int)$sort['category_id'] . "', " .
            " product_sort_order = '" . (int)$sort['sort_order'] . "'";
        $this->db->query($sql);
        
        $sort_id = $this->db->getLastId();
        if($sort_id){
            $sql = "UPDATE " . DB_PREFIX . "product_category_sort SET " .                         
                " product_sort_order = product_sort_order + 1" .
                " WHERE category_id = '" . (int)$sort['category_id'] . "' AND product_sort_order >= '" . (int)$sort['sort_order'] . "' AND product_id <> '" . (int)$sort['product_id'] . "'";            
            $this->db->query($sql);           
        }
    }
    
    public function editSortOrderManufacturer($data){
        if(!isset($data['selected'])){
            return;
        }
        
        foreach($data['selected'] as $product_id){  
            $sort = $data['sort'][$product_id];
            
            if($sort['sort_order'] != $sort['sort_order_old']){
                // Select a record will be change sort order
                $sql = "SELECT * FROM " . DB_PREFIX . "product_manufacturer_sort " .                                             
                    " WHERE manufacturer_id = '" . (int)$sort['manufacturer_id'] . "' AND IFNULL(category_id, 0) = '" . (int)$sort['category_id'] . "' AND product_msort_order = '" . (int)$sort['sort_order'] . "'";                   
                $query1 = $this->db->query($sql); 
                
                // Select sort order for record is being update
                $sql = "SELECT * FROM " . DB_PREFIX . "product_manufacturer_sort " .                                             
                    " WHERE manufacturer_id = '" . (int)$sort['manufacturer_id'] . "' AND product_id = '" . (int)$product_id . "' AND IFNULL(category_id, 0) = '" . (int)$sort['category_id'] . "'";                   
                $query2 = $this->db->query($sql); 
                
                // Update sort order for another records
                $need_update = $query1->num_rows && $query1->row['product_id'] != $product_id;
                if($need_update){                        
                    $sql = "UPDATE " . DB_PREFIX . "product_manufacturer_sort SET " .                         
                        " product_msort_order = product_msort_order + 1" .
                        " WHERE manufacturer_id = '" . (int)$sort['manufacturer_id'] . "' AND IFNULL(category_id, 0) = '" . (int)$sort['category_id'] . "' AND product_msort_order >= '" . (int)$sort['sort_order'] . "'";                   
                    
                    if(isset($query2->row['product_msort_order'])){
                        $sql .= " AND product_msort_order < '" . (int)$query2->row['product_msort_order'] . "'";
                    }
                
                    $this->db->query($sql);
                }
                
                // Process sort for current record
                if($sort['sort_id'] > 0){ 
                    if($need_update){ 
                        $sql = "UPDATE " . DB_PREFIX . "product_manufacturer_sort SET " .                         
                            " product_msort_order = '" . (int)$sort['sort_order'] . "'" .
                            " WHERE product_id = '" . (int)$product_id . "' AND manufacturer_id = '" . (int)$sort['manufacturer_id'] . "' AND IFNULL(category_id, 0) = '" . (int)$sort['category_id'] . "'";    
                    }
                    else{
                        $sql = "UPDATE " . DB_PREFIX . "product_manufacturer_sort SET " .                         
                            " product_msort_order = '" . (int)$sort['sort_order'] . "'" .
                            " WHERE msort_id = '" . (int)$sort['sort_id'] . "'";    
                    }
                }
                else{
                    $sql = "INSERT INTO " . DB_PREFIX . "product_manufacturer_sort SET " .
                        " product_id = '" . (int)$product_id . "', " .
                        " manufacturer_id = '" . (int)$sort['manufacturer_id'] . "', " .
                        " category_id = '" . (int)$sort['category_id'] . "', " .
                        " product_msort_order = '" . (int)$sort['sort_order'] . "'";                    
                }
                
                $this->db->query($sql);
            }
        }            
    }
    
    public function addSortByManufacturer($sort){
        // Manufacturer need 2 records for sort: one record for manufacturer without category and one for manufacturer with category
        $categories = array(0, $this->getTopParentCategory($sort['category_id']));
        foreach($categories as $category){
            $sql = "INSERT INTO " . DB_PREFIX . "product_manufacturer_sort SET " .
                " product_id = '" . (int)$sort['product_id'] . "', " .
                " manufacturer_id = '" . (int)$sort['manufacturer_id'] . "', " .
                " category_id = '" . $category . "', " .
                " product_msort_order = '" . (int)$sort['sort_order'] . "'";    
            $this->db->query($sql);
           
            $sort_id = $this->db->getLastId(); 
            if($sort_id){
                $sql = "UPDATE " . DB_PREFIX . "product_manufacturer_sort SET " .                         
                    " product_msort_order = product_msort_order + 1" .
                    " WHERE manufacturer_id = '" . (int)$sort['manufacturer_id'] . "' AND IFNULL(category_id, 0) = '" . $category . "' AND product_msort_order >= '" . (int)$sort['sort_order'] . "' AND product_id <> '" . (int)$sort['product_id'] . "'";                   
                $this->db->query($sql);           
            }
        }
    }
    
    public function getTopParentCategory($category_id) {
        $parent_id = (int)$category_id;
        do{
            $query = $this->db->query("SELECT parent_id FROM " . DB_PREFIX . "category WHERE category_id = '" . (int)$category_id . "'");
            if($query->num_rows && $query->row['parent_id'] > 0){
                $parent_id = (int)$query->row['parent_id'];
            };
            $category_id = (int)$query->row['parent_id'];
        }
        while($category_id > 0);        
        
        return $parent_id;
    }
    
    public function getSortOrderCategory($product_id){
        $sql = "SELECT DISTINCT * FROM " . DB_PREFIX . "product_category_sort pcs " . 
            " LEFT JOIN " . DB_PREFIX . "category c ON (pcs.category_id = c.category_id) " .
            " LEFT JOIN " . DB_PREFIX . "category_description cd ON (pcs.category_id = cd.category_id) " .
            " WHERE pcs.product_id = '" . (int)$product_id . "' AND cd.language_id = '" . (int)$this->config->get('config_language_id') . "'" .
            " ORDER BY c.sort_order, cd.name ASC"; 
        $query = $this->db->query($sql);    
        
        return $query->rows;
    }
    
    public function getSortOrderManufacturer($product_id){
        $sql = "SELECT DISTINCT * FROM " . DB_PREFIX . "product_manufacturer_sort pms " . 
            " LEFT JOIN " . DB_PREFIX . "manufacturer m ON (pms.manufacturer_id = m.manufacturer_id) " .            
            " WHERE pms.product_id = '" . (int)$product_id . 
            " ORDER BY m.sort_order, m.name ASC"; 
        $query = $this->db->query($sql);    
        
        return $query->rows;
    }
    
    public function getProductSortOrderByCategory($product_id, $categories_list){
        $sql = "SELECT DISTINCT pcs.* FROM " . DB_PREFIX . "product_category_sort pcs " . 
            " LEFT JOIN " . DB_PREFIX . "category c ON (pcs.category_id = c.category_id) " .
            " LEFT JOIN " . DB_PREFIX . "category_description cd ON (pcs.category_id = cd.category_id) " .
            " WHERE product_id = '" . (int)$product_id . "' AND cd.language_id = '" . (int)$this->config->get('config_language_id') . "'";
        
        if($categories_list){
            $sql .= " AND c.category_id IN(" . implode(',', $categories_list) . ")";
        }
        $sql .= " ORDER BY c.sort_order, cd.name ASC"; 
        
        $query = $this->db->query($sql);    
        
        return $query->rows;
    }
    
    public function getProductSortOrderByManufacturer($product_id, $manufacturer_id, $category_id){
        $sql = "SELECT DISTINCT pms.* FROM " . DB_PREFIX . "product_manufacturer_sort pms " . 
            " LEFT JOIN " . DB_PREFIX . "manufacturer m ON (pms.manufacturer_id = m.manufacturer_id) " .
            " WHERE pms.product_id = '" . (int)$product_id . "' AND IFNULL(pms.category_id, 0) = '" . (int)$category_id . "'";
        
        if($manufacturer_id){
            $sql .= " AND m.manufacturer_id = '" . (int)$manufacturer_id . "'";
        }
        $sql .= " ORDER BY m.sort_order, m.name ASC";  
         
        $query = $this->db->query($sql);
        
        return $query->row;
    }
    
    public function getProducts($data = array()) {
		if ($data) {
            if (isset($data['filter_category']) && !is_null($data['filter_category'])) {
				$category_id = (int)$data['filter_category'];			
			}
            else{
                $category_id = 0;
            }
            
			$sql = "SELECT p.*,pd.name, cd.name AS category, m.name AS manufacturer, pms.product_msort_order " .
                " FROM " . DB_PREFIX . "product p" .
                " LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id)" .
                " LEFT JOIN " . DB_PREFIX . "product_to_category p2c ON (p.product_id = p2c.product_id)" .
                " LEFT JOIN " . DB_PREFIX . "category_description cd ON (p2c.category_id = cd.category_id)" .
                //" LEFT JOIN " . DB_PREFIX . "product_category_sort pcs ON (p.product_id = pcs.product_id AND p2c.category_id = pcs.category_id)" .
                " LEFT JOIN " . DB_PREFIX . "manufacturer m ON (m.manufacturer_id = p.manufacturer_id)" .
                " LEFT JOIN " . DB_PREFIX . "product_manufacturer_sort pms ON (p.product_id = pms.product_id AND m.manufacturer_id = pms.manufacturer_id AND IFNULL(pms.category_id, 0) = '" . $category_id . "')" .                 
                " WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "'";
		
			if (isset($data['filter_name']) && !is_null($data['filter_name'])) {
				$sql .= " AND LCASE(pd.name) LIKE '%" . strtolower($this->db->escape(strtolower($data['filter_name']))) . "%'";
			}

			if (isset($data['filter_model']) && !is_null($data['filter_model'])) {
				$sql .= " AND LCASE(p.model) LIKE '%" . $this->db->escape(strtolower($data['filter_model'])) . "%'";
			}
                        
            if (isset($data['filter_category']) && !is_null($data['filter_category'])) {
				$categories = $this->getCategoriesList($data['filter_category']);
			
				$sql .= " AND p2c.category_id IN (" . $categories . ")";
			}
			
            if (isset($data['filter_sku']) && !is_null($data['filter_sku'])) {
                $sql .= " AND p.sku LIKE '%" . $this->db->escape($data['filter_sku']) . "%'";
            }
            
            if (isset($data['filter_manufacturer']) && !is_null($data['filter_manufacturer'])) {
                $sql .= " AND p.manufacturer_id = '" . $this->db->escape($data['filter_manufacturer']) . "'";
            }
            
			if (isset($data['filter_quantity']) && !is_null($data['filter_quantity'])) {
				$sql .= " AND p.quantity = '" . $this->db->escape($data['filter_quantity']) . "'";
			}
			
			if (isset($data['filter_status']) && !is_null($data['filter_status'])) {
				$sql .= " AND p.status = '" . (int)$data['filter_status'] . "'";
			}

            if (isset($data['filter_price']) && !is_null($data['filter_price'])) {
                $sql .= " AND p.price = '" . (int)$data['filter_price'] . "'";
            }
            
			$sort_data = array(
				'pd.name',
				'p.model',
                'category',
                'p.sku',
                'manufacturer',
				'p.quantity',
				'p.status',
				'p.sort_order',
                'p.price',   
                //'pcs.product_sort_order',
                'pms.product_msort_order',
			);	
			$sql .= " GROUP BY pd.product_id"  ;

			if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
				$sql .= " ORDER BY " . $data['sort'];	
			} else {
				$sql .= " ORDER BY pd.name";	
			}
			
			if (isset($data['order']) && ($data['order'] == 'DESC')) {
				$sql .= " DESC";
			} else {
				$sql .= " ASC";
			}
		
			if (isset($data['start']) || isset($data['limit'])) {
				if ($data['start'] < 0) {
					$data['start'] = 0;
				}				

				if ($data['limit'] < 1) {
					$data['limit'] = 20;
				}	
			
				$sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
			}	
			        
			$query = $this->db->query($sql); 
		
			return $query->rows;
		} else {
			$product_data = $this->cache->get('product.' . $this->config->get('config_language_id'));
		
			if (!$product_data) {
				$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "' ORDER BY pd.name ASC");
	
				$product_data = $query->rows;
			
				$this->cache->set('product.' . $this->config->get('config_language_id'), $product_data);
			}	
	
			return $product_data;
		}
	}	
	
	public function getTotalProducts($data = array()) {
        if(isset($data['filter_category']) && !is_null($data['filter_category'])){
            $category_id = (int)$data['filter_category'];			
        }
        else{
            $category_id = 0;
        }
          
		//$sql = "SELECT COUNT(*) AS total FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_category p2c ON (p.product_id = p2c.product_id) LEFT JOIN " . DB_PREFIX . "category_description cd ON (p2c.category_id = cd.category_id) WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "'";
        $sql = "SELECT COUNT(DISTINCT(p.product_id)) AS total FROM " . DB_PREFIX . "product p" .
            " LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id)" .
            " LEFT JOIN " . DB_PREFIX . "product_to_category p2c ON (p.product_id = p2c.product_id)" .
            " LEFT JOIN " . DB_PREFIX . "category_description cd ON (p2c.category_id = cd.category_id)" .
            " LEFT JOIN " . DB_PREFIX . "manufacturer m ON (m.manufacturer_id = p.manufacturer_id)" .
            " LEFT JOIN " . DB_PREFIX . "product_manufacturer_sort pms ON (p.product_id = pms.product_id AND m.manufacturer_id = pms.manufacturer_id AND IFNULL(pms.category_id, 0) = '" . $category_id . "')" .                 
            " WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "'";
		
		if (isset($data['filter_name']) && !is_null($data['filter_name'])) {
			$sql .= " AND LCASE(pd.name) LIKE '%" . $this->db->escape(strtolower($data['filter_name'])) . "%'";
		}

        if (isset($data['filter_category']) && !is_null($data['filter_category'])) {
			$categories = $this->getCategoriesList($data['filter_category']);
			
            $sql .= " AND p2c.category_id IN (" . $categories . ")";
        }
            
		if (isset($data['filter_model']) && !is_null($data['filter_model'])) {
			$sql .= " AND LCASE(p.model) LIKE '%" . $this->db->escape(strtolower($data['filter_model'])) . "%'";
		}
		        
        if (isset($data['filter_sku']) && !is_null($data['filter_sku'])) {
            $sql .= " AND p.sku LIKE '%" . $this->db->escape($data['filter_sku']) . "%'";
        }
        
        if (isset($data['filter_manufacturer']) && !is_null($data['filter_manufacturer'])) {
            $sql .= " AND p.manufacturer_id = '" . $this->db->escape($data['filter_manufacturer']) . "'";
        }
        
		if (isset($data['filter_quantity']) && !is_null($data['filter_quantity'])) {
			$sql .= " AND p.quantity = '" . $this->db->escape($data['filter_quantity']) . "'";
		}
		
		if (isset($data['filter_status']) && !is_null($data['filter_status'])) {
			$sql .= " AND p.status = '" . (int)$data['filter_status'] . "'";
		}
        
		$query = $this->db->query($sql);
		
		return $query->row['total'];
	}
	
	private function getCategoriesList($category_id){
        $sql = "SELECT category_id FROM category WHERE status = '1' AND parent_id = '" . (int)$category_id . "'";
        $query = $this->db->query($sql); 
        
        $rows = array($category_id);
        $level1 = array();
        $categories = $query->rows;
        foreach($categories as $row){
            $rows[] = $row['category_id'];
            $level1[] = $row['category_id']; 
        }
       
        if(!empty($level1)){            
            $sql = "SELECT category_id FROM category WHERE status = '1' AND parent_id IN (" . implode(',', $level1) . ")";
            
            $query = $this->db->query($sql); 
            $level2 = $query->rows; 
            foreach($level2 as $row_item){
                $rows[] = $row_item['category_id'];                
            }            
        } 
                                                   
        $list = implode(',', $rows);            
        
        return $list;
    }
}
?>
